package com.b2c.repository.ecom;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;

import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import com.b2c.entity.result.PagingResponse;
import com.b2c.entity.ecom.KeywordDataEntity;
import com.b2c.entity.ecom.KeywordsEntity;

@Repository
public class EcomKeywordRepository {
    @Resource
    private JdbcTemplate jdbcTemplate;

    @Transactional
    public PagingResponse<KeywordsEntity> getList(Integer pageIndex, Integer pageSize,String category,String category2,String platform,String source,String keyword){
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT SQL_CALC_FOUND_ROWS kw.*");
        // sb.append(",(SELECT MIN(sousuoredu) FROM ecom_keyword_data WHERE keywordId=kw.id) AS minSouSuoReDu");
        // sb.append(",(SELECT MAX(sousuoredu) FROM ecom_keyword_data WHERE keywordId=kw.id) AS maxSouSuoReDu");
        sb.append(",(SELECT sousuoredu FROM ecom_keyword_data WHERE keywordId=kw.id ORDER BY includeDate desc LIMIT 1) AS sousuoredu");
        sb.append(",(SELECT dianjiredu FROM ecom_keyword_data WHERE keywordId=kw.id ORDER BY includeDate desc LIMIT 1) AS dianjiredu");
        sb.append(",(SELECT dianjilv FROM ecom_keyword_data WHERE keywordId=kw.id ORDER BY includeDate desc LIMIT 1) AS dianjilv");
        sb.append(",(SELECT zhifulv FROM ecom_keyword_data WHERE keywordId=kw.id ORDER BY includeDate desc LIMIT 1) AS zhifulv");
        sb.append(",(SELECT includeDate FROM ecom_keyword_data WHERE keywordId=kw.id ORDER BY includeDate desc LIMIT 1) AS includeDate");
        sb.append(",(SELECT shichangchujia FROM ecom_keyword_data WHERE keywordId=kw.id ORDER BY includeDate desc LIMIT 1) AS shichangchujia");
        sb.append(" FROM ecom_keywords as kw ");
        sb.append(" where kw.isDelete=0 ");

        List<Object> params = new ArrayList<>();
        if (StringUtils.isEmpty(keyword) == false) {
            sb.append(" AND kw.keyword LIKE ? ");
            params.add("%"+keyword+"%");
        }
        if (StringUtils.isEmpty(platform) == false) {
            sb.append(" AND kw.platform = ?");
            params.add(platform);
        }
        if (StringUtils.isEmpty(category) == false) {
            sb.append(" AND kw.category = ?");
            params.add(category);
        }
        if (StringUtils.isEmpty(category2) == false) {
            sb.append(" AND kw.category2 = ?");
            params.add(category2);
        }
        

        sb.append(" ORDER BY kw.id DESC LIMIT ?,? ");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        List<KeywordsEntity> list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(KeywordsEntity.class), params.toArray(new Object[params.size()]));
        Integer totalSize = getTotalSize();
        return new PagingResponse<>(pageIndex, pageSize, totalSize, list);
    }
    

    protected int getTotalSize() {
        return jdbcTemplate.queryForObject("SELECT FOUND_ROWS() as row_num;", int.class);
    }

    /**
     * 添加
     * @param entity
     */
    @Transactional
    public void addKeyWord(KeywordsEntity entity,KeywordDataEntity dataEntity){
   
        String sql = "INSERT INTO ecom_keywords (category,category2,keyword,source,platform,remark) VALUE (?,?,?,?,?,?)";
        // jdbcTemplate.update(sql,entity.getCategory(),entity.getCategory2(),entity.getKeyword(),entity.getSource()
        // ,entity.getPlatform(),entity.getRemark());

        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
                ps.setString(1, entity.getCategory());
                ps.setString(2, entity.getCategory2());
                ps.setString(3, entity.getKeyword());
                ps.setString(4, entity.getSource());
                ps.setString(5, entity.getPlatform());
                ps.setString(6, entity.getRemark());
                return ps;
            }
        }, keyHolder);

        Long keywordId = keyHolder.getKey().longValue();
        if(dataEntity!=null){
            //添加数据
            String dataSQL = "INSERT INTO ecom_keyword_data (keywordId,rank,goodsCount,sousuorenqi,sousuoredu,dianjirenqi,dianjiredu,dianjilv,zhifulv,jingzhengzhishu,chengjiaozhishu,shichangchujia,includeDate) VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?)";
            jdbcTemplate.update(dataSQL,keywordId,dataEntity.getRank(),dataEntity.getGoodsCount(),dataEntity.getSousuorenqi(),dataEntity.getSousuoredu(),dataEntity.getDianjirenqi(),dataEntity.getDianjiredu()
            ,dataEntity.getDianjilv(),dataEntity.getZhifulv(),dataEntity.getJingzhengzhishu(),dataEntity.getChengjiaozhishu(),dataEntity.getShichangchujia(),dataEntity.getIncludeDate());
        }


    }

    @Transactional
    public PagingResponse<KeywordDataEntity> getDataList(Integer pageIndex, Integer pageSize, Long keywordId) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT SQL_CALC_FOUND_ROWS kw.* FROM ecom_keyword_data as kw");
        sb.append(" where kw.isDelete=0 ");

        List<Object> params = new ArrayList<>();
        sb.append(" AND kw.keywordId = ?");
        params.add(keywordId);
        
        sb.append(" ORDER BY kw.id DESC LIMIT ?,? ");
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        List<KeywordDataEntity> list = jdbcTemplate.query(sb.toString(), new BeanPropertyRowMapper<>(KeywordDataEntity.class), params.toArray(new Object[params.size()]));
        Integer totalSize = getTotalSize();
        return new PagingResponse<>(pageIndex, pageSize, totalSize, list);
    }

    public KeywordsEntity getKeywordById(Long keywordId) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT SQL_CALC_FOUND_ROWS kw.* FROM ecom_keywords as kw");
        sb.append(" where kw.id=? ");
        return jdbcTemplate.queryForObject(sb.toString(),new BeanPropertyRowMapper<>(KeywordsEntity.class),keywordId);
    }

    public void addKeywordData(KeywordDataEntity entity) {
        String sql = "INSERT INTO ecom_keyword_data (keywordId,rank,goodsCount,sousuorenqi,sousuoredu,dianjirenqi,dianjiredu,dianjilv,zhifulv,jingzhengzhishu,chengjiaozhishu,shichangchujia,includeDate) VALUE (?,?,?,?,?,?,?,?,?,?,?,?,?)";
        jdbcTemplate.update(sql,entity.getKeywordId(),entity.getRank(),entity.getGoodsCount(),entity.getSousuorenqi(),entity.getSousuoredu(),entity.getDianjirenqi(),entity.getDianjiredu()
        ,entity.getDianjilv(),entity.getZhifulv(),entity.getJingzhengzhishu(),entity.getChengjiaozhishu(),entity.getShichangchujia(),entity.getIncludeDate());
    }
    
}
